SELECT 
            purchase.id, 
            purchase.code, 
            CONCAT('[', JSON_OBJECT('id', CONCAT(purchase.provider_id), 'displayText',  CONCAT(provider.code, ' - ', provider.name)), ']') AS provider_id,
            purchase.provider_email,
            provider.code AS providerCode,
            provider.name AS providerName,
            provider.avatar AS providerAvatar,
            provider.avatarColor AS providerAvatarColor, 
            provider.avatarColorLetter AS providerAvatarColorLetter,
            provider.avatarLetter AS providerAvatarLetter,
            provider.phone AS providerPhone,
            CONCAT('$', IFNULL(SUM(purchaseVsexpenses.amount), 0)) AS total_expenses,
            purchase.price,
            '' AS check_mail,
            purchase.payment_status,
            DATE_FORMAT(purchase.purchase_date, '%m/%d/%Y') AS purchaseDate,
            DATE_FORMAT(purchase.due_date, '%m/%d/%Y') AS purchaseDueDate,
            DATE_FORMAT(purchase.delivered_date, '%m/%d/%Y') AS purchaseDeliveredDate, 
            purchase.purchase_date, 
            purchase.due_date,
            purchase.bill_date,
            purchase.creation_date,
            purchase.delivered_date, 
            purchase.fill_bill_date, 
            purchase.shipping, 
            purchase.investment, 
            purchase.revenue, 
            purchase.status, 
            purchase.earning, 
            purchase.min_amount,
            IFNULL(SUM(CAST(purchaseVsitem.total_price AS DECIMAL(10, 2))), 0) AS general_total_price,
            CONCAT(FORMAT(purchase.general_total_expenses,2)) AS general_total_expenses,
            CONCAT('$', FORMAT(purchase.general_total_price,2)) AS general_total_price_dt,
            CONCAT(FORMAT(purchase.general_total_price,2)) AS general_total_price,
            CONCAT(FORMAT(purchase.general_total_selling_price,2)) AS general_total_selling_price,
            purchase.notes,
            purchase.purchase_statement,
            purchase.purchase_footer,
            purchase.reference_number,
            CONCAT(purchase.code,' / ',purchase.reference_number ) AS purchaseCodeREF_td,
            purchase.spent_credit,
            CONCAT('$', FORMAT( ((purchase.general_total_price + purchase.spent_credit) - (purchase.paid_amount + purchase.credit_amount )),2)) AS due_amount,
            
            CONCAT(
            '[',
                
                '{',
                    '"tableName":"chats"', 
                    ',"data":[',
                                  GROUP_CONCAT(
                                        DISTINCT(
                                        JSON_OBJECT(
                                            'id', chat_ia.id,
                                            'avatar', users.avatar,
                                            'question', chat_ia.question,
                                            'answare', chat_ia.answare,
                                            'userId', chat_ia.userId,
                                            'dateTime', chat_ia.dateTime         
                                        )

                                        ) SEPARATOR ','),
                            ']',
                '}',  
            
            ']'
            ) AS chats,
            CONCAT(
            '[',
                
                '{',
                    '"data":[',
                                  GROUP_CONCAT(
                                        DISTINCT(
                                        JSON_OBJECT(
                                            'id',CONCAT(purchaseVsitem.id),
                                            'qty_available', CONCAT(ROUND((purchaseVsitem.qty- purchaseVsitem.sold_qty)))
                                                )

                                        ) SEPARATOR ','),
                            ']',
                '}',  
            
            ']'
            ) AS inventory_stock,

            CONCAT(
            '[',
                
                '{',
                    '"tableName":"entitychanges"', 
                    ',"data":[',
                                  GROUP_CONCAT(
                                        DISTINCT(
                                        JSON_OBJECT(
                                            'id',   CONCAT(entity_audit.id), 
                                            'user_name',CONCAT(AES_DECRYPT(UNHEX(__name), 'uValKP2n94W8200cq'), ' ', users.last_name),
                                            'date_change',DATE_FORMAT(entity_audit.date_registered, '%m/%d/%Y %H:%i:%s'),                       
                                            'avatar',CONCAT(users.avatar),               
                                            'changes',CONCAT(entity_audit.changes)          
                                                )

                                        ) SEPARATOR ','),
                            ']',
                '}',  
            
            ']'
            ) AS entitychanges,

        CONCAT(
        '[',
            
            '{',
                '"tableName":"purchase_vs_item"', 
                ',"data":[',
                                GROUP_CONCAT(
                                DISTINCT(
                                JSON_OBJECT(
                                    'id',CONCAT(purchaseVsitem.id),
                                    'item_id', CONCAT(purchaseVsitem.item_id),
                                    'type_selling', CONCAT(purchaseVsitem.type_selling),
                                    'qty', CONCAT(purchaseVsitem.qty),
                                    'unit_price', CONCAT(purchaseVsitem.unit_price),
                                    'selling_price', CONCAT(purchaseVsitem.selling_price),
                                    'total_price', CONCAT(purchaseVsitem.total_price),
                                    'cogs_row_amount', CONCAT(purchaseVsitem.cogs_row_amount)
                                )) SEPARATOR ','),
                          ']',
            '}', 

            ',{',
                '"tableName":"purchase_vs_investment"', 
                ',"data":[',
                                GROUP_CONCAT(
                                DISTINCT(
                                JSON_OBJECT(
                                    'id',CONCAT(purchaseVsinvestment.id),
                                    'investorId', CONCAT(purchaseVsinvestment.investorId),
                                    'invested_amount', CONCAT(purchaseVsinvestment.invested_amount),
                                    'revenue', CONCAT(purchaseVsinvestment.revenue),
                                    'revenue_amount', CONCAT(purchaseVsinvestment.revenue_amount),
                                    'status', purchaseVsinvestment.status
                                )) SEPARATOR ','),
                          ']',
            '}',

            ',{',
                '"tableName":"purchase_vs_expenses"', 
                ',"data":[',
                                GROUP_CONCAT(
                                DISTINCT(
                                JSON_OBJECT(
                                    'id',CONCAT(purchaseVsexpenses.id),
                                    'provider_id', CONCAT(purchaseVsexpenses.provider_id),
                                    'expenses_account_id', CONCAT(purchaseVsexpenses.expenses_account_id),
                                    'date', purchaseVsexpenses.pveDate,
                                    'amount', CONCAT(purchaseVsexpenses.amount)
                                )) SEPARATOR ','),
                          ']',
            '}',  

            ',{',
                '"tableName":"purchase_tracking_mail"', 
                ',"data":[',
                                GROUP_CONCAT(
                                DISTINCT(
                                JSON_OBJECT(
                                    'id',CONCAT(mailTracking.id),
                                    'email', mailTracking.email,
                                    'mail_date', mailTracking.mail_date,
                                    'mail_time', mailTracking.mail_time,
                                    'providerId', CONCAT(mailTracking.providerId),
                                    'user_name', user_name
                                )) SEPARATOR ','),
                          ']',
            '}', 

            ',{',
                '"tableName":"purchase_vs_lost"', 
                ',"data":[',
                                GROUP_CONCAT(
                                DISTINCT(
                                JSON_OBJECT(
                                    'id',CONCAT(purchaseVslost.id),
                                    'purchase_vs_itemId', CONCAT(purchaseVslost.purchase_vs_itemId),
                                    'return_item', purchaseVslost.return_item,
                                    'lost_qty', CONCAT(purchaseVslost.lost_qty),
                                    'lost_price', CONCAT(purchaseVslost.lost_price),
                                    'date', purchaseVslost.date,
                                    'notes', CONCAT(purchaseVslost.notes)
                                )) SEPARATOR ','),
                          ']',
            '}',  
        
        ']'
        ) AS multiTables
        FROM purchase 
        LEFT JOIN provider ON provider.id = purchase.provider_id 

        LEFT JOIN (
            SELECT purchase_tracking_mail.id AS id,
            purchase_tracking_mail.purchaseId AS purchaseId,
            purchase_tracking_mail.email AS email,
            purchase_tracking_mail.mail_date AS mail_date,
            purchase_tracking_mail.mail_time AS mail_time,
            CONCAT('[', JSON_OBJECT('id', CONCAT(purchase_tracking_mail.providerId), 'displayText',  CONCAT(provider.name)), ']') AS providerId,
            CONCAT(AES_DECRYPT(UNHEX(__name), 'uValKP2n94W8200cq'), ' ', users.last_name) AS user_name
            FROM purchase_tracking_mail
            LEFT JOIN provider ON provider.id = purchase_tracking_mail.providerId
            LEFT JOIN users ON users.id = purchase_tracking_mail.userId
            WHERE purchase_tracking_mail.purchaseId = '2351'
        ) AS mailTracking ON mailTracking.purchaseId = purchase.id

        LEFT JOIN (
            SELECT 
                purchase_vs_item.id AS id, 
                CONCAT('[', JSON_OBJECT('id', CONCAT(purchase_vs_item.item_id),'displayText',  CONCAT(item.name, ' | ', item.brand),
                    'otherField', JSON_OBJECT('id', purchase_vs_item.id, 'sold_qty', purchase_vs_item.sold_qty, 'returned_qty', purchase_vs_item.returned_qty, 
                    'unit_price', purchase_vs_item.unit_price,   'qty', purchase_vs_item.qty,
                     'purchase_vs_itemId', purchase_vs_item.id, 'name', item.name) ), ']') AS item_id,
                CONCAT('[', JSON_OBJECT('id', CONCAT(purchase_vs_item.type_selling),'displayText',  CONCAT(item_unit.name)), ']') AS type_selling,
                purchase_vs_item.qty AS qty,
                CASE WHEN purchase_vs_item.unit_price <> 0.00 THEN purchase_vs_item.unit_price ELSE '' END AS unit_price, 
                CASE WHEN purchase_vs_item.selling_price <> 0.00 THEN purchase_vs_item.selling_price ELSE '' END AS selling_price,
                CASE WHEN purchase_vs_item.total_price <> 0.00 THEN purchase_vs_item.total_price ELSE '' END AS total_price,
                purchase_vs_item.purchaseId AS purchaseId,
                purchase_vs_item.cogs_row_amount AS cogs_row_amount,
                purchase_vs_item.sold_qty

            FROM 
                purchase_vs_item 
                LEFT JOIN item ON item.id = purchase_vs_item.item_id 
                LEFT JOIN item_unit ON item_unit.id = purchase_vs_item.type_selling 
            WHERE 
                purchase_vs_item.deleted = '0' 
                AND purchase_vs_item.purchaseId = '2351'
        ) AS purchaseVsitem ON purchaseVsitem.purchaseId = purchase.id

        LEFT JOIN (
            SELECT purchase_vs_investment.id AS id, 
            CONCAT('[', JSON_OBJECT('id', CONCAT(purchase_vs_investment.investorId),'displayText',  CONCAT(investor.name)), ']') AS investorId, 
            purchase_vs_investment.invested_amount AS invested_amount, 
            COALESCE(
            NULLIF(purchase_vs_investment.revenue, 0),
            p.revenue
        ) AS revenue,
            purchase_vs_investment.invested_amount * COALESCE(NULLIF(purchase_vs_investment.revenue, 0), p.revenue) AS revenue_amount,
            purchase_vs_investment.status AS status, 
            purchase_vs_investment.purchaseId AS purchaseId 
            FROM purchase_vs_investment 
            LEFT JOIN investor ON investor.id = purchase_vs_investment.investorId 
            LEFT JOIN (
                SELECT revenue, id FROM purchase WHERE id = '2351' AND deleted = '0'
            ) AS p ON p.id = purchase_vs_investment.purchaseId
            WHERE purchase_vs_investment.deleted = '0' 
            AND purchase_vs_investment.purchaseId = '2351'
        ) AS purchaseVsinvestment ON purchaseVsinvestment.purchaseId = purchase.id

        LEFT JOIN (
            SELECT purchase_vs_expenses.id AS id, 
            CONCAT('[', JSON_OBJECT('id', CONCAT(purchase_vs_expenses.provider_id),'displayText',  CONCAT(provider.code, ' - ', provider.name)), ']') AS provider_id,
            CONCAT('[', JSON_OBJECT('id', CONCAT(purchase_vs_expenses.expenses_account_id),'displayText',  CONCAT(accounts.name)), ']') AS expenses_account_id,
            purchase_vs_expenses.date AS pveDate,
            CASE WHEN purchase_vs_expenses.amount <> 0.00 THEN purchase_vs_expenses.amount ELSE '' END AS amount,
            CONCAT(purchase_vs_expenses.purchaseId) AS purchaseId
            FROM purchase_vs_expenses 
            INNER JOIN provider ON provider.id = purchase_vs_expenses.provider_id 
            INNER JOIN accounts ON accounts.id = purchase_vs_expenses.expenses_account_id
            WHERE purchase_vs_expenses.deleted = '0' 
            AND purchase_vs_expenses.purchaseId = '2351'
        ) AS purchaseVsexpenses ON purchaseVsexpenses.purchaseId = purchase.id
        
        LEFT JOIN (
            SELECT 
                purchase_vs_lost.id AS id, 
                purchase_vs_lost.purchaseId AS purchaseId, 
                CONCAT('[', 
                    JSON_OBJECT(
                                'id',   CONCAT(purchase_vs_lost.purchase_vs_itemId), 
                                'displayText', CONCAT(item.name,' - ',  item_unit.name, ' - ', 
                                 ROUND(purchase_vs_item.qty - purchase_vs_item.sold_qty)),
                                'otherField', JSON_OBJECT('unit_price', purchase_vs_item.unit_price,
                                                            'item_name', item.name,
                                                            'po', purchase.code,  
                                                            'purchase_vs_itemId',   CONCAT(purchase_vs_lost.purchase_vs_itemId),                                                                               
                                                            'qty_available', (purchase_vs_item.qty - purchase_vs_item.sold_qty))
                                    
                                ),
                ']') as purchase_vs_itemId, 
                purchase_vs_lost.date AS date,
                purchase_vs_lost.lost_qty AS lost_qty,
                purchase_vs_lost.notes as notes,
                purchase_vs_lost.return_item,
                purchase_vs_lost.lost_price

            FROM 
                purchase_vs_lost 
                INNER JOIN purchase on purchase.id = purchase_vs_lost.purchaseId and purchase.deleted = '0'
                INNER JOIN purchase_vs_item on purchase_vs_item.id = purchase_vs_lost.purchase_vs_itemId and purchase_vs_item.deleted = '0'
                LEFT JOIN item ON item.id = purchase_vs_item.item_id 
                LEFT JOIN item_unit ON item_unit.id = purchase_vs_item.type_selling 
            WHERE 
                purchase_vs_lost.deleted = '0' 
                AND purchase_vs_lost.purchaseId = '2351'
        ) AS purchaseVslost ON purchaseVslost.purchaseId = purchase.id

        LEFT JOIN chat_ia ON purchase.id = chat_ia.entityId
        LEFT JOIN entity_audit ON entity_audit.entity_id = purchase.id AND entity_audit.entity = 'purchase'
        LEFT JOIN users ON users.id = entity_audit.user_id

        WHERE purchase.id = '2351'  AND purchase.deleted = '0' GROUP BY purchase.id;